﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Partner.Utility
{
    public static class ExcelUtil
    {
        /// <summary>
        /// 读取excel到DataTable（不支持合并列头的格式）
        /// </summary>
        /// <param name="fileName">Excel路径</param>
        /// <param name="bar">进度条控件</param>
        /// <returns></returns>
        //public static System.Data.DataTable LoadData(string fileName)
        //{
        //    System.Data.DataTable dt = new System.Data.DataTable();
        //    Microsoft.Office.Interop.Excel.Application app;
        //    app = new Microsoft.Office.Interop.Excel.Application();
        //    try
        //    {
        //        var wbs = app.Workbooks;
        //        wbs.Add(fileName);
        //        var ws = (Worksheet)app.Worksheets.get_Item(1);
        //        int rows = ws.UsedRange.Rows.Count;
        //        int columns = ws.UsedRange.Columns.Count;

        //        dt.TableName = ws.Name;
        //        //反射调用进度条属性
        //        PropertyInfo _Maximum, _Value = null;
        //        for (int i = 1; i < rows + 1; i++)
        //        {
        //            DataRow dr = dt.NewRow();
        //            bool _empty = true;//标记row是否为空
        //            for (int j = 1; j <= columns; j++)
        //            {
        //                if (i == 1)
        //                    dt.Columns.Add(app.Cells[i, j].ToStringOrEmpty());
        //                else
        //                    dr[j - 1] = app.Cells[i, j].Text;
        //                if (!string.IsNullOrEmpty(app.Cells[i, j].Text))
        //                    _empty = false;//非空
        //            }
        //            if (!_empty && i > 1)
        //                dt.Rows.Add(dr);
        //            if (_Value != null)
        //                _Value.SetValue(bar, i, null);
        //        }
        //    }
        //    catch
        //    {
        //        return dt;
        //    }
        //    finally
        //    {
        //        KillProcess(app);
        //    }
        //    return dt;
        //}

        /// <summary>
        /// 导出Excel后，杀死Excel进程
        /// </summary>
        /// <param name="app"></param>
        //private static void KillProcess(Microsoft.Office.Interop.Excel.Application app)
        //{
        //    IntPtr t = new IntPtr(app.Hwnd);
        //    int k = 0;
        //    Win32Api.GetWindowThreadProcessId(t, out k);
        //    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
        //    p.Kill();
        //}

        /// <summary>
        /// 读取excel第一行的列头
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="isXlsx"></param>
        /// <returns></returns>
        public static List<string> ExcelHeaderToList(string filePath, bool isXlsx)
        {
            ISheet sheet = null;
            List<string> listStr = new List<string>();
            listStr.Add(string.Empty);
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (isXlsx)//isXlsx可能是错误的
                {
                    try
                    {
                        XSSFWorkbook workbook = new XSSFWorkbook(fs);
                        sheet = workbook.GetSheetAt(0);
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook workbook = new HSSFWorkbook(fs2);
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    try
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(fs);
                        sheet = workbook.GetSheetAt(0);
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        XSSFWorkbook workbook = new XSSFWorkbook(fs2);
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        listStr.Add(firstRow.GetCell(i).StringCellValue);
                    }
                }
            }
            catch
            {
                return listStr;
            }
            return listStr;
        }

        /// <summary>
        ///  读取excel列头
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<string> ExcelHeaderToList(string filePath)
        {
            ISheet sheet = null;
            List<string> listStr = new List<string>();
            listStr.Add(string.Empty);
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = null;
                string fileExtend = System.IO.Path.GetExtension(filePath);
                if (fileExtend.ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook(fs);
                else
                    workbook = new HSSFWorkbook(fs);
                sheet = workbook.GetSheetAt(0);
                fs.Close();
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        listStr.Add(firstRow.GetCell(i).StringCellValue);
                    }
                }
            }
            catch
            {
                return listStr;
            }
            return listStr;
        }

        /// <summary>
        ///  读取excel列头
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<string> ExcelHeaderToList(Stream fs, bool isXlsx = false)
        {
            ISheet sheet = null;
            List<string> listStr = new List<string>();
            listStr.Add(string.Empty);
            try
            {
                IWorkbook workbook = null;
                if (isXlsx)
                    workbook = new XSSFWorkbook(fs);
                else
                    workbook = new HSSFWorkbook(fs);
                sheet = workbook.GetSheetAt(0);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        listStr.Add(firstRow.GetCell(i).StringCellValue);
                    }
                }
            }
            catch
            {
                return listStr;
            }
            return listStr;
        }
        /// <summary>
        /// NPIO读取Excel
        /// </summary>
        /// <param name="filePath">文件位置</param>
        /// <param name="bar">进度条</param>
        /// <param name="isXlsx">Excel类型</param>
        /// <param name="KeepSame">Excel模型是否保持一致性，保留中间的空行空列(默认不保存)</param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelToDataTable(string filePath, bool isXlsx, bool KeepSame = false)
        {
            ISheet sheet = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            int startRow = 0;
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (isXlsx)//isXlsx可能是错误的
                {
                    try
                    {
                        XSSFWorkbook workbook = new XSSFWorkbook(fs);
                        sheet = workbook.GetSheetAt(0);
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook workbook = new HSSFWorkbook(fs2);
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    try
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(fs);
                        sheet = workbook.GetSheetAt(0);
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        XSSFWorkbook workbook = new XSSFWorkbook(fs2);
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                        dt.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            if (KeepSame)//保持一致则全行赋值
                            {
                                DataRow dtRow = dt.NewRow();
                                int k = 0;
                                for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                                {
                                    dtRow[k] = "";
                                    k++;
                                }
                                dt.Rows.Add(dtRow);
                            }
                            else
                                continue;
                        }
                        else
                        {
                            DataRow dtRow = dt.NewRow();
                            int k = 0;
                            for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dtRow[k] = row.GetCell(j).ToString();
                                }
                                k++;
                            }
                            dt.Rows.Add(dtRow);
                        }
                    }
                }
            }
            catch
            {
                return dt;
            }
            finally
            {
            }
            return dt;
        }

        /// <summary>
        /// NPIO读取Excel
        /// </summary>
        /// <param name="filePath">文件位置</param>
        /// <param name="sheetName">指定表名</param>
        /// <param name="bar">进度条</param>
        /// <param name="isXlsx">Excel类型</param>
        /// <param name="KeepSame">Excel模型是否保持一致性，保留中间的空行空列(默认不保存)</param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelToDataTable(string filePath, string sheetName, bool isXlsx, bool KeepSame = false)
        {
            ISheet sheet = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            int startRow = 0;
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (isXlsx)//isXlsx可能是错误的
                {
                    try
                    {
                        XSSFWorkbook workbook = new XSSFWorkbook(fs);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            for (int i = 0; i < workbook.NumberOfSheets; i++)
                            {
                                sheet = workbook.GetSheetAt(i);
                                if (sheet != null && sheet.GetRow(0) != null)
                                    break;
                            }
                        }
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        HSSFWorkbook workbook = new HSSFWorkbook(fs2);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            for (int i = 0; i < workbook.NumberOfSheets; i++)
                            {
                                sheet = workbook.GetSheetAt(i);
                                if (sheet != null && sheet.GetRow(0) != null)
                                    break;
                            }
                        }
                    }
                }
                else
                {
                    try
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(fs);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            for (int i = 0; i < workbook.NumberOfSheets; i++)
                            {
                                sheet = workbook.GetSheetAt(i);
                                if (sheet != null && sheet.GetRow(0) != null)
                                    break;
                            }
                        }
                    }
                    catch
                    {
                        FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                        XSSFWorkbook workbook = new XSSFWorkbook(fs2);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            for (int i = 0; i < workbook.NumberOfSheets; i++)
                            {
                                sheet = workbook.GetSheetAt(i);
                                if (sheet != null && sheet.GetRow(0) != null)
                                    break;
                            }
                        }
                    }
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                        dt.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            if (KeepSame)//保持一致则全行赋值
                            {
                                DataRow dtRow = dt.NewRow();
                                int k = 0;
                                for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                                {
                                    dtRow[k] = "";
                                    k++;
                                }
                                dt.Rows.Add(dtRow);
                            }
                            else
                                continue;
                        }
                        else
                        {
                            DataRow dtRow = dt.NewRow();
                            int k = 0;
                            for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dtRow[k] = row.GetCell(j).ToString();
                                }
                                k++;
                            }
                            dt.Rows.Add(dtRow);
                        }
                    }
                }
            }
            catch
            {
                return dt;
            }
            finally
            {
            }
            return dt;
        }

        #region 读取单个Excel多表

        /// <summary>
        /// NPIO读取Excel(全sheet)（同步bar用于显示进度;异步不给）
        /// </summary>
        /// <param name="filePath">文件位置</param>
        /// <param name="isXlsx">Excel类型</param>
        /// <param name="KeepSame">Excel模型是否保持一致性，保留中间的空行空列</param>
        /// <param name="bar">进度条</param>
        /// <returns>表名及对应表数据</returns>
        public static Dictionary<string, System.Data.DataTable> ExcelToListDataTable(string filePath, bool isXlsx, bool KeepSame = true)
        {
            Dictionary<string, System.Data.DataTable> result = new Dictionary<string, System.Data.DataTable>();
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = null;
                int sheetNum = 1;
                if (isXlsx)
                {
                    workbook = new XSSFWorkbook(fs);
                    sheetNum = workbook.NumberOfSheets;
                }
                else
                {
                    workbook = new HSSFWorkbook(fs);
                    sheetNum = workbook.NumberOfSheets;
                }
                for (int i = 0; i < sheetNum; i++)
                {
                    string name = workbook.GetSheetName(i);
                    var dt = SheetToDataTable(i, workbook, sheetNum, KeepSame);
                    result.Add(name, dt);
                }
            }
            catch
            {
                throw new BizException("读取异常");
            }
            return result;
        }

        public static System.Data.DataTable SheetToDataTable(int SheetIndex, IWorkbook workbook, int SheetNum, bool KeepSame = false)
        {
            ISheet sheet = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            int startRow = 0;
            try
            {
                double _progress = (double)SheetIndex / SheetNum;
                sheet = workbook.GetSheetAt(SheetIndex);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        DataColumn column = new DataColumn(firstRow.GetCell(i) != null ? firstRow.GetCell(i).StringCellValue : i.ToString());
                        try //同列名临时处理办法？
                        {
                            dt.Columns.Add(column);
                        }
                        catch
                        {
                            dt.Columns.Add(new DataColumn(i.ToString()));
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            if (KeepSame)//保持一致则全行赋值
                            {
                                DataRow dtRow = dt.NewRow();
                                int k = 0;
                                for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                                {
                                    dtRow[k] = "";
                                    k++;
                                }
                                dt.Rows.Add(dtRow);
                            }
                            else
                                continue;
                        }
                        else
                        {
                            DataRow dtRow = dt.NewRow();
                            int k = 0;
                            for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dtRow[k] = row.GetCell(j).ToString();
                                }
                                k++;
                            }
                            dt.Rows.Add(dtRow);
                        }
                    }
                }
            }
            catch
            {
                return dt;
            }
            finally
            {
            }
            return dt;
        }

        #endregion 读取单个Excel多表

        #region 判断单元格是否被合并

        public static bool IsMergedRegionCell(int cellIndex, int rowIndex, ISheet sheet, ref int firstRegionRow)
        {
            bool isMerged = false;
            var regionLists = GetMergedCellRegion(sheet);

            foreach (var cellRangeAddress in regionLists)
            {
                for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
                {
                    if (rowIndex == i)
                    {
                        for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
                        {
                            if (cellIndex == j)
                            {
                                isMerged = true;
                                firstRegionRow = cellRangeAddress.FirstRow;
                                break;
                            }
                            else
                            {
                                continue;
                            }
                        }
                    }
                    else
                    {
                        continue;
                    }
                }
            }

            return isMerged;
        }



        // 获取合并区域信息
        private static List<CellRangeAddress> GetMergedCellRegion(ISheet sheet)
        {
            int mergedRegionCellCount = sheet.NumMergedRegions;
            var returnList = new List<CellRangeAddress>();

            for (int i = 0; i < mergedRegionCellCount; i++)
            {
                returnList.Add(sheet.GetMergedRegion(i));
            }

            return returnList;
        }

        #endregion 判断单元格是否被合并

        /// <summary>
        /// List 导出至Excel
        /// </summary>
        /// <typeparam name="T">类</typeparam>
        /// <param name="TitleNameArray">列头名称</param>
        /// <param name="listPropertyName">列头对应的属性名称</param>
        /// <param name="list">需要导出的数据集合</param>
        /// <param name="filepath">路径</param>
        /// <returns></returns>
        public static bool ListTToExcel<T>(string[] TitleNameArray, List<string> listPropertyName, List<T> list, string filepath) where T : new()
        {
            if (list == null || list.Count < 1)
                return false;
            IWorkbook workbook = null;
            string fileExtend = System.IO.Path.GetExtension(filepath);
            if (fileExtend.ToLower() == ".xlsx")
                workbook = new XSSFWorkbook();
            else
                workbook = new HSSFWorkbook();
            try
            {
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);
                for (int i = 0; i < TitleNameArray.Length; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(TitleNameArray[i].ToString());
                }
                int rowIndex = 1;
                var obj = list[0];
                var type = obj.GetType();
                var pros = type.GetProperties();
                for (int i = 0; i < list.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (var p in pros)
                    {
                        if (listPropertyName.Contains(p.Name))
                        {
                            int index = 0;
                            for (int j = 0; j < listPropertyName.Count; j++)
                            {
                                if (listPropertyName[j] == p.Name)
                                {
                                    index = j;
                                    break;
                                }
                            }
                            dataRow.CreateCell(index).SetCellValue(p.GetValue(list[i], null).ToStringOrEmpty());
                        }
                    }
                    rowIndex++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        data = null;
                    }
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 导出数据集合到CSV流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="titleNames">列头</param>
        /// <param name="propertyNames">属性</param>
        /// <param name="list">数据集合</param>
        /// <returns></returns>
        public static MemoryStream ListToCSV<T>(string[] titleNames, string[] propertyNames, List<T> list)
        {
            List<string> rows = new List<string> {
                string.Join(",", titleNames)
            };
            var properties = typeof(T).GetProperties();
            list.ForEach(p =>
            {
                var row = new List<string>();
                foreach (var propName in propertyNames)
                {
                    var prop = properties.FirstOrDefault(_prop => _prop.Name.ToLower() == propName.ToLower());
                    if (!prop.IsNullEmpty())
                    {
                        var val = prop.GetValue(p, null);
                        row.Add(val.ToStringOrEmpty());
                    }

                }
                rows.Add(string.Join(",", row));
            });
            var result = string.Join("\n", rows);
            return new MemoryStream(Encoding.UTF8.GetBytes(result));
        }

        /// <summary>
        /// 导出数据集合到Excel流（XSSF/HSSF）
        /// 2018-8-3 合并xlsx  /  xls
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="titleNames">列头</param>
        /// <param name="propertyNames">属性</param>
        /// <param name="list">数据集合</param>
        /// <param name="ixXLSX">是不是Xlsx</param>
        /// <returns></returns>
        public static MemoryStream ListTToExcelMS<T>(string[] titleNames, string[] propertyNames, List<T> list, bool ixXLSX = false)
        {
            IWorkbook workbook = null;
            if (ixXLSX)
                workbook = new XSSFWorkbook();//XSSF
            else
                workbook = new HSSFWorkbook();//HSSF
            var sheet = workbook.CreateSheet();
            var headerRow = sheet.CreateRow(0);
            for (int colNum = 0; colNum < titleNames.Length; colNum++)
            {
                headerRow.CreateCell(colNum).SetCellValue(titleNames[colNum]);
            }
            var type = list[0].GetType();
            var properties = type.GetProperties();
            for (int rowNum = 0; rowNum < list.Count; rowNum++)
            {
                IRow row = sheet.CreateRow(rowNum + 1);
                for (int colNum = 0; colNum < propertyNames.Length; colNum++)
                {
                    var propName = propertyNames[colNum];
                    var prop = properties.FirstOrDefault(p => p.Name.ToLower() == propName.ToLower());
                    var val =
                        prop == null ? string.Empty : prop.GetValue(list[rowNum], null).ToStringOrEmpty();
                    var cell = row.CreateCell(colNum);
                    cell.SetCellType(CellType.String);//使用文本输出（因为碰到了身份证被科学计数的问题）
                    cell.SetCellValue(val);
                }
            }

            var ms = new NPOIMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;
            return ms;
        }

        /// <summary>
        /// List 导出至Excel
        /// </summary>
        /// <typeparam name="T">类</typeparam>
        /// <param name="list">需要导出的数据集合</param>
        /// <param name="_Columns">DataGridViewEx的column</param>
        /// <param name="filepath">文件保存路径</param>
        /// <param name="_timeDel">时分秒为0时自动删除具体时间</param>
        /// <param name="_exlist">需要导出的扩展数据集合</param>
        /// <returns>是否成功保存</returns>
        public static bool ListToExcel<T>(List<T> list, string filepath, bool _timeDel = true, List<T> _exList = null) where T : new()
        {
            try
            {
                if (list == null || list.Count < 1)
                    return false;
                List<string> TitleNameArray = new List<string>();
                List<string> listPropertyName = new List<string>();
                //for (int i = 0, j = 0; i < _Columns.Count; i++)
                //{
                //    if (_Columns[i].Visible)
                //    {
                //        TitleNameArray.Insert(j, _Columns[i].HeaderText);
                //        listPropertyName.Insert(j++, _Columns[i].DataPropertyName);
                //    }
                //}
                //新建工作表
                IWorkbook workbook = null;
                string fileExtend = System.IO.Path.GetExtension(filepath);
                if (fileExtend.ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook();
                else
                    workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);
                //输出表头
                for (int i = 0; i < TitleNameArray.Count; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(TitleNameArray[i].ToString());
                }
                int rowIndex = 1;
                var pros = list[0].GetType().GetProperties();
                //输出具体数据
                for (int i = 0; i < list.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (var p in pros)
                    {
                        if (listPropertyName.Contains(p.Name))
                        {
                            int index = 0;
                            for (int j = 0; j < listPropertyName.Count; j++)
                            {
                                if (listPropertyName[j] == p.Name)
                                {
                                    index = j;
                                    break;
                                }
                            }
                            var _val = p.GetValue(list[i], null).ToStringOrEmpty();
                            //对时间字段，时分秒皆为0的删除具体时间
                            if (p.PropertyType.FullName.IndexOf("System.DateTime") > -1 && _val != string.Empty && _timeDel)
                            {
                                try
                                {
                                    if (Convert.ToDateTime(_val).Hour == 0 && Convert.ToDateTime(_val).Minute == 0 && Convert.ToDateTime(_val).Second == 0)
                                    {
                                        _val = Convert.ToDateTime(_val).ToShortDateString();
                                    }
                                }
                                catch { }
                            }

                            if (p.PropertyType.FullName.IndexOf("System.Boolean") > -1 && _val != string.Empty)
                            {
                                if (_val == "True" || _val == "true") _val = "是";
                                else if (_val == "False" || _val == "false") _val = "否";
                            }
                            //_val.Length ? Encoding.UTF8.GetBytes(_val).Length
                            if (Encoding.UTF8.GetBytes(_val).Length > 32767)
                                _val = Encoding.UTF8.GetBytes(_val).ToStringUTF8().Substring(0, 32760);
                            try
                            {
                                dataRow.CreateCell(index).SetCellValue(_val);
                            }
                            catch
                            {
                                dataRow.CreateCell(index).SetCellValue("异常数据");
                            }
                        }
                    }
                    rowIndex++;
                }
                //保存
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        data = null;
                    }
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 导出所有数据，对数据不做处理
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static bool ListAllToExcel<T>(List<T> list, string filepath) where T : new()
        {
            try
            {
                IWorkbook workbook = null;
                string fileExtend = System.IO.Path.GetExtension(filepath);
                if (fileExtend.ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook();
                else
                    workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                int j = 0, rowCount = 0;
                IRow headerRow = sheet.CreateRow(rowCount);
                //输出表头
                var pros = list[0].GetType().GetProperties();
                foreach (var i in pros)
                {
                    headerRow.CreateCell(j++).SetCellValue(i.Name);
                }

                foreach (var item in list)
                {
                    j = 0;
                    IRow nextRow = sheet.CreateRow(++rowCount);
                    foreach (var p in pros)
                    {
                        try
                        {
                            var _val = p.GetValue(item, null).ToStringOrEmpty();
                            nextRow.CreateCell(j++).SetCellValue(_val);
                        }
                        catch { }
                    }
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        data = null;
                    }
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Excel文件转成List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath">文件路径</param>
        /// <param name="_keyValues">Key为字段中文名称，Value为字段的数据库名称</param>
        /// <param name="menuStart">数据开始行数，默认为0</param>
        /// <returns></returns>
        public static List<T> ExcelToList<T>(string filePath, Dictionary<string, string> _keyValues, int menuStart = 0) where T : new()
        {
            List<T> list = new List<T>();
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                ISheet sheet = null;
                try
                {
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);
                    sheet = workbook.GetSheetAt(0);
                }
                catch
                {
                    FileStream fs2 = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                    HSSFWorkbook workbook = new HSSFWorkbook(fs2);
                    sheet = workbook.GetSheetAt(0);
                }

                if (sheet != null)
                {
                    int rowCount = sheet.LastRowNum;
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;

                    for (int i = 1; i <= rowCount; i++)
                    {
                        T item = new T();
                        //遍历每一行数据
                        for (int k = 0; k <= cellCount; k++)
                        {
                            //单元格数据输入item
                            ICell cell = sheet.GetRow(i).GetCell(k);
                            object cellValue = null;
                            if (cell == null) continue;

                            switch (cell.CellType)
                            {
                                case CellType.String: //文本
                                    cellValue = cell.StringCellValue;
                                    break;

                                case CellType.Numeric: //数值
                                    cellValue = Convert.ToInt32(cell.NumericCellValue);//Double转换为int
                                    break;

                                case CellType.Boolean: //bool
                                    cellValue = cell.BooleanCellValue;
                                    break;

                                case CellType.Blank: //空白
                                    cellValue = "";
                                    break;

                                default:
                                    cellValue = "ERROR";
                                    break;
                            }

                            try
                            {
                                var _key = _keyValues[sheet.GetRow(menuStart).GetCell(k).ToString()];
                                //时间格式处理
                                if (typeof(T).GetProperty(_key).ToString().IndexOf("String") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToString(cellValue), null);
                                else if (typeof(T).GetProperty(_key).ToString().IndexOf("DateTime") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToDateTime(cellValue), null);
                                else if (typeof(T).GetProperty(_key).ToString().IndexOf("Decimal") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToDecimal(cellValue), null);
                                else if (typeof(T).GetProperty(_key).ToString().IndexOf("Boolean") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToBoolean(cellValue), null);
                                else if (typeof(T).GetProperty(_key).ToString().IndexOf("Double") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToDouble(cellValue), null);
                                else if (typeof(T).GetProperty(_key).ToString().IndexOf("Int") > -1)
                                    typeof(T).GetProperty(_key).SetValue(item, Convert.ToInt32(cellValue), null);
                                else
                                    typeof(T).GetProperty(_key).SetValue(item, cellValue, null);
                            }
                            catch { }
                        }

                        list.Add(item);
                    }
                }

                return list;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 将键值对保存为Excel
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="_keyValues">Key对应字段的数据库字段，Value为中文名称</param>
        public static bool SaveNewExcel(string filepath, Dictionary<string, string> _keyValues, List<string> dicListRequired = null)
        {
            try
            {
                IWorkbook workbook = null;
                string fileExtend = System.IO.Path.GetExtension(filepath);
                if (fileExtend.ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook();
                else
                    workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);
                dicListRequired = new List<string>();
                var style = workbook.CreateCellStyle();
                style.BorderDiagonalColor = (short)10;

                int i = 0;
                foreach (var _one in _keyValues)
                {
                    headerRow.CreateCell(i++).SetCellValue(_one.Value);
                    if (dicListRequired.Contains(_one.Value) || dicListRequired.Contains(_one.Key))
                    {
                        headerRow.Cells[i - 1].CellStyle = style;
                    }
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        data = null;
                    }
                }
                return true;
            }
            catch { return false; }
        }

        /// <summary>
        /// DataTable To Excel
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public static BizResult<bool> DataTableToExcel(System.Data.DataTable dt, string filePath)
        {
            BizResult<bool> result = new BizResult<bool>() { Success = true, Data = true };
            if (dt == null || dt.Rows.Count < 1)
            {
                result.Success = false;
                result.Message = "没有数据可以导出,请检查";
                return result;
            }
            try
            {
                IWorkbook workbook = null;
                string fileExtend = System.IO.Path.GetExtension(filePath);
                if (fileExtend.ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook();
                else
                    workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();
                IRow headerRow = sheet.CreateRow(0);

                // handling header.
                foreach (DataColumn column in dt.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

                int rowIndex = 1;
                foreach (DataRow row in dt.Rows)//暂没设置单元格值类型
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dt.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToStringOrEmpty());
                    }
                    rowIndex++;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    ms.Flush();
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        data = null;
                    }
                }
            }
            catch
            {
                result.Success = false;
                result.Message = "导出过程中出现异常，请关闭界面后重新打开尝试导出";
            }
            return result;
        }

        /// <summary>
        /// 读取Excel并转换成DataTable
        /// </summary>
        /// <param name="filePath">Excel文件地址</param>
        /// <param name="isColumnName">Excel是否包含了列头（跳过列头）</param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelToDataTable(string filePath, bool isColumnName = true)
        {
            System.Data.DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            string fileExtend = System.IO.Path.GetExtension(filePath);
            if (fileExtend.ToLower() == ".xlsx")
                workbook = new XSSFWorkbook();
            else
                workbook = new HSSFWorkbook();
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    //#region 对不同版本的Excel处理（上面已处理）

                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook == null) return dataTable;

                    //#endregion 对不同版本的Excel处理

                    dataTable = new System.Data.DataTable();

                    // （单元格，读不到图片）读取第一个 sheet，当然也可以循环读取每个sheet
                    sheet = workbook.GetSheetAt(0);
                    // 读图片
                    var pics = sheet.GetAllPictureInfos();

                    // 假设不存在只有图片而没有内容的情况
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//总行数  
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(0);//第一行  
                            int cellCount = firstRow.LastCellNum;//列数  

                            //构建datatable的列  
                            if (isColumnName)
                            {
                                startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    cell = firstRow.GetCell(i);
                                    if (cell == null || cell.StringCellValue == null) continue;

                                    column = new DataColumn(cell.StringCellValue);
                                    dataTable.Columns.Add(column);
                                }
                            }
                            else
                            {
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    // 如果没有列头，则手动指定一个 column1、column2 这样的列头
                                    column = new DataColumn("column" + (i + 1));
                                    dataTable.Columns.Add(column);
                                }
                            }

                            //填充行  
                            for (int i = startRow; i <= rowCount; ++i)
                            {
                                row = sheet.GetRow(i);
                                if (row == null) continue;

                                dataRow = dataTable.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        dataRow[j] = "";
                                        // 假设一张图片只占一格
                                        var pic = pics.Where(p => p.MinRow == i && p.MinCol == j).FirstOrDefault();
                                        if (pic != null)
                                        {
                                            // 序列化后存储，否则变为“System.Byte[]”字符串。
                                            dataRow[j] = Newtonsoft.Json.JsonConvert.SerializeObject(pic.PictureData);
                                        }
                                    }
                                    else
                                    {
                                        //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                        switch (cell.CellType)
                                        {
                                            case CellType.Blank:
                                                dataRow[j] = "";
                                                break;

                                            case CellType.Numeric:
                                                short format = cell.CellStyle.DataFormat;
                                                //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                if (format == 14 || format == 31 || format == 57 || format == 58)
                                                    dataRow[j] = cell.DateCellValue;
                                                else
                                                    dataRow[j] = cell.NumericCellValue;
                                                break;

                                            case CellType.String:
                                                dataRow[j] = cell.StringCellValue;
                                                break;
                                        }
                                    }
                                }
                                dataTable.Rows.Add(dataRow);
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

        public static void CreateErrorExcel(string source, string target, List<int> lines)
        {
            IWorkbook workbook;
            var ext = Path.GetExtension(source);
            using (FileStream fs = new FileStream(source, FileMode.Open, FileAccess.Read))
            {
                if (ext == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    workbook = new HSSFWorkbook(fs);
                }
            }
            var sheet = workbook.GetSheetAt(0);
            if (sheet == null)
                throw new BizException(string.Format("地址为{0}的Excel文件不含有Sheet页", source));

            ICellStyle styleRed = workbook.CreateCellStyle();
            styleRed.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            styleRed.FillPattern = FillPattern.SolidForeground;

            ICellStyle styleNormal = workbook.CreateCellStyle();
            styleNormal.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL;
            styleNormal.FillPattern = FillPattern.SolidForeground;

            var cellNum = sheet.GetRow(0).LastCellNum;
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                for (int j = 0; j < cellNum; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell == null)//确保单元格存在
                        cell = row.CreateCell(j);
                    if (lines.Contains(i - 1))
                        cell.CellStyle = styleRed;
                    else //非错误行重置颜色
                        cell.CellStyle = styleNormal;
                }
            }

            using (FileStream fs = new FileStream(target, FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }


        public static MemoryStream CreateErrorExcelStream(string source, List<int> lines)
        {
            IWorkbook workbook;
            var ext = Path.GetExtension(source);
            using (FileStream fs = new FileStream(source, FileMode.Open, FileAccess.Read))
            {
                if (ext == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    workbook = new HSSFWorkbook(fs);
                }
            }
            var sheet = workbook.GetSheetAt(0);
            if (sheet == null)
                throw new BizException(string.Format("地址为{0}的Excel文件不含有Sheet页", source));

            ICellStyle styleRed = workbook.CreateCellStyle();
            styleRed.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            styleRed.FillPattern = FillPattern.SolidForeground;

            ICellStyle styleNormal = workbook.CreateCellStyle();
            styleNormal.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL;
            styleNormal.FillPattern = FillPattern.SolidForeground;

            var cellNum = sheet.GetRow(0).LastCellNum;
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                for (int j = 0; j < cellNum; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell == null)//确保单元格存在
                        cell = row.CreateCell(j);
                    if (lines.Contains(i - 1))
                        cell.CellStyle = styleRed;
                    else //非错误行重置颜色
                        cell.CellStyle = styleNormal;
                }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                return ms;
            }
        }

        /// <summary>
        /// 导出数据集合到Excel流（HSSF）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="titleNames">列头</param>
        /// <param name="propertyNames">属性</param>
        /// <param name="list">数据集合</param>
        /// <returns></returns>
        public static MemoryStream ListToXLS<T>(string[] titleNames, string[] propertyNames, List<T> list)
        {
            var workbook = new HSSFWorkbook();//HSSF 
            var sheet = workbook.CreateSheet();
            var headerRow = sheet.CreateRow(0);
            for (int colNum = 0; colNum < titleNames.Length; colNum++)
            {
                headerRow.CreateCell(colNum).SetCellValue(titleNames[colNum]);
            }
            var type = list[0].GetType();
            var properties = type.GetProperties();
            for (int rowNum = 0; rowNum < list.Count; rowNum++)
            {
                IRow row = sheet.CreateRow(rowNum + 1);
                for (int colNum = 0; colNum < propertyNames.Length; colNum++)
                {
                    var propName = propertyNames[colNum];
                    var prop = properties.FirstOrDefault(p => p.Name.ToLower() == propName.ToLower());
                    var val =
                        prop == null ? string.Empty : prop.GetValue(list[rowNum], null).ToStringOrEmpty();
                    row.CreateCell(colNum).SetCellValue(val);
                }
            }

            var ms = new NPOIMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;
            return ms;
        }

        /// <summary>
        /// 导出数据集合到Excel流（XSSF）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="titleNames">列头</param>
        /// <param name="propertyNames">属性</param>
        /// <param name="list">数据集合</param>
        /// <returns></returns>
        public static MemoryStream ListToXLSX<T>(string[] titleNames, string[] propertyNames, List<T> list)
        {
            var workbook = new XSSFWorkbook();//XSSF
            var sheet = workbook.CreateSheet();
            var headerRow = sheet.CreateRow(0);
            for (int colNum = 0; colNum < titleNames.Length; colNum++)
            {
                headerRow.CreateCell(colNum).SetCellValue(titleNames[colNum]);
            }
            var type = list[0].GetType();
            var properties = type.GetProperties();
            for (int rowNum = 0; rowNum < list.Count; rowNum++)
            {
                IRow row = sheet.CreateRow(rowNum + 1);
                for (int colNum = 0; colNum < propertyNames.Length; colNum++)
                {
                    var propName = propertyNames[colNum];
                    var prop = properties.FirstOrDefault(p => p.Name.ToLower() == propName.ToLower());
                    var val =
                        prop == null ? string.Empty : prop.GetValue(list[rowNum], null).ToStringOrEmpty();
                    var cell = row.CreateCell(colNum);
                    cell.SetCellType(CellType.String);//使用文本输出（因为碰到了身份证被科学计数的问题）
                    cell.SetCellValue(val);
                }
            }

            var ms = new NPOIMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;
            return ms;
        }

        public static IDataValidation CreateDataValidation(this XSSFDataValidationHelper helper, int firstCol, int lastCol, string[] data)
        {
            //下拉项总字符长度大于255时需要特殊处理

            CellRangeAddressList regions = new CellRangeAddressList(1, 65535, firstCol, lastCol);//约束范围：c2到c65535  
                                                                                                 //XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);//获得一个数据验证Helper              
            IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(data), regions);//创建约束
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");//不符合约束时的提示  
            validation.ShowErrorBox = true;//显示上面提示 = True  

            return validation;
        }
        public static IDataValidation CreateDataValidationFormulaList(this XSSFDataValidationHelper helper, int firstCol, int lastCol, string[] data,ISheet sheet)
        {
            //先创建一个Sheet专门用于存储下拉项的值，并将各下拉项的值写入其中：
            ISheet sheet2 = sheet.Workbook.CreateSheet("ShtDictionary");
            for (int i = 0; i < data.Length; i++)
            {
                sheet2.CreateRow(i).CreateCell(0).SetCellValue(data[i]);
            }

            //然后定义一个名称，指向刚才创建的下拉项的区域：
            IName range = sheet2.Workbook.CreateName();
            range.RefersToFormula = "ShtDictionary!$A$1:$A$" + data.Length;
            range.NameName = "dicRange";
            //最后，设置数据约束时指向这个名称而不是字符数组：

            CellRangeAddressList regions = new CellRangeAddressList(1, 65535, firstCol, firstCol);
            IDataValidation dataValidate = helper.CreateValidation(helper.CreateFormulaListConstraint("dicRange"), regions);
            return dataValidate;
        }

        public static IDataValidation CreateXSSFValidation(int firstCol, int lastCol, string[] data, ISheet sheet)
        {

            CellRangeAddressList regions = new CellRangeAddressList(1, 65535, firstCol, lastCol);//约束范围：c2到c65535  
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);//获得一个数据验证Helper              
            IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(data), regions);//创建约束
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");//不符合约束时的提示  
            validation.ShowErrorBox = true;//显示上面提示 = True  

            return validation;
        }
        /// <summary>
        /// 添加表头和验证信息
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowlist"></param>
        public static void AddXSSFValidation(this ISheet sheet, List<ExcleTempRow> rowlist)
        {
            var newStyle = sheet.Workbook.CreateCellStyle();
            IFont font = sheet.Workbook.CreateFont();
            font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
            newStyle.SetFont(font);

            IRow row = sheet.CreateRow(0);
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);//获得一个数据验证Helper              
            var rs = rowlist.OrderBy(r => r.Rank);
            var rowIndex = 0;
            foreach (var cell in rs)
            {
                if (cell.Require)
                    row.CreateCell(rowIndex, cell.Title, newStyle);
                else
                    row.CreateCell(rowIndex).SetCellValue(cell.Title);

                if (cell.ValidationData != null&&cell.ValidationData.Length>0)
                {
                    if (cell.ValidationData.Aggregate(0, (s, i) => s += i.Length + 1) < 255)
                    {
                        sheet.AddValidationData(helper.CreateDataValidation(rowIndex, rowIndex, cell.ValidationData));//添加进去  
                    }
                    else
                    {
                        sheet.AddValidationData(helper.CreateDataValidationFormulaList(rowIndex, rowIndex, cell.ValidationData,sheet ));//添加进去  
                    }
                }
                rowIndex++;
            }

            sheet.ForceFormulaRecalculation = true;
        }

        public static ICell CreateCell(this IRow row, int colnum, string value, ICellStyle cellStyle)
        {
            var cell = row.CreateCell(colnum);
            cell.CellStyle = cellStyle;
            cell.SetCellValue(value);
            return cell;
        }
        /// <summary>
        /// 创建带验证信息的 xlsx workbook
        /// </summary>
        /// <param name="rows"></param>
        /// <param name="sheetTitle"></param>
        /// <returns></returns>
        public static IWorkbook CreateXSSFWorkbookHasValidation(List<ExcleTempRow> rows, string sheetTitle)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetTitle);
            sheet.AddXSSFValidation(rows);
            return workbook;
        }

        /// <summary>
        /// 创建带验证信息的xlsx 流
        /// </summary>
        /// <param name="rows"></param>
        /// <param name="sheetTitle"></param>
        /// <returns></returns>
        public static NPOIMemoryStream CreateXSSFHasValidationStream(List<ExcleTempRow> rows, string sheetTitle)
        {
            NPOIMemoryStream memory = new NPOIMemoryStream();
            var workbook = CreateXSSFWorkbookHasValidation(rows, sheetTitle);
            memory.AllowClose = false;
            workbook.Write(memory);
            memory.Flush();
            memory.Position = 0;    // 指定内存流起始值
            return memory;
        }
    }
    /// <summary>
    /// 生成导入模板的模型类
    /// </summary>
    public class ExcleTempRow
    {

        public string Title { get; set; }
        public bool Require { get; set; }
        /// <summary>
        /// 单元格的验证数据
        /// </summary>
        public string[] ValidationData { get; set; }
        /// <summary>
        /// 排序号 默认升序
        /// </summary>
        public int Rank { get; set; }
        public ExcleTempRow()
        {

        }
        public ExcleTempRow(string title, string[] vd = null, int rank = 0, bool isReq = false)
        {
            Title = title;
            if (vd != null)
                ValidationData = vd;
            Rank = rank;
            Require = isReq;
        }

    }

    /// <summary>
    /// 合并单元格的位置
    /// </summary>
    public class MergedStyle
    {
        public int RowFromIndex { get; set; }
        public int ColFromIndex { get; set; }
        public int RowToIndex { get; set; }
        public int ColToIndex { get; set; }

        /// <summary>
        /// 持续周期，仅适用规则比较统一的(默认0持续1次)
        /// </summary>
        public int Duration { get; set; }
    }

    #region Excel 图片读取扩展

    public static class NpoiExtend
    {
        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
        {
            return sheet.GetAllPictureInfos(null, null, null, null);
        }

        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal = true)
        {
            if (sheet is HSSFSheet)
            {
                return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else if (sheet is XSSFSheet)
            {
                return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else
            {
                throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
            }
        }

        private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
            var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
            if (null != shapeContainer)
            {
                var shapeList = shapeContainer.Children;
                foreach (var shape in shapeList)
                {
                    if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
                    {
                        var picture = (HSSFPicture)shape;
                        var anchor = (HSSFClientAnchor)shape.Anchor;
                        if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                        {
                            picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                        }
                    }
                }
            }
            return picturesInfoList;
        }



        private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
            var documentPartList = sheet.GetRelations();
            foreach (var documentPart in documentPartList)
            {
                if (documentPart is XSSFDrawing)
                {
                    var drawing = (XSSFDrawing)documentPart;
                    var shapeList = drawing.GetShapes();
                    foreach (var shape in shapeList)
                    {
                        if (shape is XSSFPicture)
                        {
                            var picture = (XSSFPicture)shape;
                            var anchor = picture.GetPreferredSize();
                            if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                            {
                                picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                            }
                        }
                    }
                }
            }
            return picturesInfoList;
        }

        private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
            int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
        {
            int _rangeMinRow = rangeMinRow ?? pictureMinRow;
            int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
            int _rangeMinCol = rangeMinCol ?? pictureMinCol;
            int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;
            if (onlyInternal)
            {
                return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                    _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
            }
            else
            {
                return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
                    (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
            }
        }
    }

    public class PicturesInfo
    {
        public int MinRow { get; set; }
        public int MaxRow { get; set; }
        public int MinCol { get; set; }
        public int MaxCol { get; set; }
        public Byte[] PictureData { get; private set; }

        public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinCol = minCol;
            this.MaxCol = maxCol;
            this.PictureData = pictureData;
        }
    }

    #endregion Excel 图片读取扩展

    /// <summary>
    /// 重写过的内存流，来防止出现“已关闭”现象
    /// <para>参考自：https://www.cnblogs.com/codedreams/p/5662740.html </para>
    /// </summary>
    public class NPOIMemoryStream : MemoryStream
    {
        public NPOIMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }

}
